package com.onlineMIS.unit_testing;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.aspectj.apache.bcel.classfile.Field;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Restrictions;

import com.mysql.jdbc.log.Log;
import com.onlineMIS.ORM.entity.headQ.custMgmt.HeadQCust;
import com.onlineMIS.common.loggerLocal;
import com.onlineMIS.unit_testing.entity.TestUser;

public class testTwo {

	public static void main(String[] args) throws IOException {
		
		FileInputStream stream  = null;
		try {
			stream = new FileInputStream(new File("D:\\test\\客户2.xls"));
			
			 HSSFWorkbook workbook = new HSSFWorkbook(stream);
		        // 获取第一个工作表（这里可以选择工作表，如果你想获取其他工作表，只需将索引值替换为相应的数字即可）
		        HSSFSheet sheet = workbook.getSheetAt(0);
		        // 获取表格第一行和最后一行的索引值
		        int firstRowNum = sheet.getFirstRowNum();
		        int lastRowNum = sheet.getLastRowNum()-2;

		        List<TestUser>list = new ArrayList<TestUser>();
		                        
		        for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
		            // 获取当前行
		            Row row = sheet.getRow(rowNum);
		            // 获取姓名、岗位和电话单元格
		            Cell cellId = row.getCell(0);
		            Cell cellName = row.getCell(2);
		            Cell cellMoney = row.getCell(27);//欠款
		            // 从单元格中获取对应的数据
		            int id = (int)cellId.getNumericCellValue();		           
		            String name = cellName.getStringCellValue();
		            double amountMoney = cellMoney.getNumericCellValue();
	
		            TestUser user = new TestUser();
		            user.setId(id);
		            user.setName(name);
		            user.setAmountMoney(amountMoney);
		            list.add(user);    
		        }

		        List<TestUser> setUser = new ArrayList<TestUser>();// 重复客户
		        List<Integer> ids = new ArrayList<Integer>();  // 重复客户ids
		        List<TestUser> listUser = new ArrayList<TestUser>();// 不重复客户
		        // 添加重复客户和重复客户id
		        for (int i = 0; i < list.size()-1; i++) {
		        	for (int j = list.size()-1; j>i;j--) {
						if (list.get(j).getName().equals(list.get(i).getName())) {
							setUser.add(list.get(j));
							ids.add(list.get(j).getId());
							loggerLocal.info("重复客户id是："+list.get(j).getId());
						}
					}
				}
		        // 添加不重复的客户
		        for (TestUser testUser : list) {
					if (!ids.contains(testUser.getId())) {
						listUser.add(testUser);
					}
				}
		        
		        List<String> excleUserNames = new ArrayList<String>();
				for (TestUser testUser : listUser) {
					// excle表中客户初始金不为0,并且不存在数据库中
					if (testUser.getAmountMoney() != 0 ) {
						excleUserNames.add(testUser.getName());						
					}else {
						continue;
					}
			        
				}
		        
		        testTwo(listUser,excleUserNames);
		        
		    for (Integer id : ids) {
				System.out.println("重复客户id:"+id);
			}    
		        
  
		  
		     
		        
		} finally {
			stream.close();
		}
	}
	
	// 修改客户初始化金额
	private	static void testTwo(List<TestUser> list, List<String> excleUserNames) {
		
		Configuration configuration = new Configuration().configure();
		
		SessionFactory sFactory = configuration.buildSessionFactory();
		
		Session session = sFactory.openSession();
		
		Criteria criteria = session.createCriteria(HeadQCust.class);
		
		List<String> sqlUserNames = new ArrayList<String>();
		//拿到数据库中所有客户
		List<HeadQCust> cList = criteria.list();
		for (HeadQCust hQCust : cList) {
			sqlUserNames.add(hQCust.getName());
			if (hQCust.getInitialAcctBalance() !=0 ) {
				
			}
			
			TestUser testUser = new TestUser();
			for (TestUser user : list) {
				if (hQCust.getName().equals(user.getName())) {
					testUser = user;
				}
				
				
				
			}
			HeadQCust custDetail =  geTwo(hQCust.getName(),testUser.getAmountMoney());
			
		}

		//去重
		excleUserNames.removeAll(sqlUserNames);

		
		for (String setUserName : excleUserNames) {
			 writeReport(setUserName, "D:\\test\\user.txt");
		}
		
		session.close();
		
	};
	
	public static HeadQCust geTwo(String name,Double money) {
		Configuration configuration = new Configuration().configure();
		
		SessionFactory sFactory = configuration.buildSessionFactory();
		
		Session session = sFactory.openSession();
		
		Criteria criteria = session.createCriteria(HeadQCust.class);
		
		criteria.add(Restrictions.eq("name",name));
		List<HeadQCust> custDetail =  criteria.list();
		HeadQCust headQCust = custDetail.get(0);
		headQCust.setInitialAcctBalance(money);
		session.update(headQCust);
		session.flush();
		session.close();
		sFactory.close();
	
		return custDetail.get(0);
		
	}
	
	
	
	/**
	 *
	 * @param content 内容
	 * @param filePath 文件路径
	 * @return
	 */
	public static void writeReport(String content,String filePath) {
	    BufferedWriter out = null;
	    try {
	        File file = new File(filePath);
	        if (!file.exists()) {
	            file.getParentFile().mkdirs();
	            file.createNewFile();
	        }
	        //编码格式可自己更换
	        out = new BufferedWriter(new OutputStreamWriter(
	                new FileOutputStream(filePath, true), "UTF-8"));
	        out.write("'"+content+"',");
	        //out.write(content + "\r\n");
	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {
	        try {
	            out.close();
	 
	        } catch (IOException e) {
	            e.printStackTrace();
	 
	        }
	    }
	}

}
